package com.jdbc.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.bean.Course;

public class StudentSelectDB {
	private Connection connection = null;
	private PreparedStatement preStatement = null;
	private ResultSet resultSet = null;
	private final String user = "xiaotao";
	private final String password = "mojintao233";
	private final String url = "jdbc:mysql://www.xiaotao2333.top/selectCourse";

	/*
	 * 获取连接
	 */
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(url, user, password);
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	/*
	 * 回收资源
	 */

	public void release()
	{
		try
		{
			if(resultSet!=null)
			{
				resultSet.close();
			}
			if(preStatement!=null)
			{
				preStatement.close();
			}
			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

	}

	/*
	 *学生选课
	 */
	public List<String> selectCourse(String userName,String[] courseId)
	{
		List<String> failCoursesList=new ArrayList<String>();
		for(String id:courseId) {
			int availableNumber = getRemainCourseByCourseId(id);
			if (availableNumber > 0) {
				try {
					preStatement = connection.prepareStatement("insert into elective(username,course_id) values(?,?)");
					preStatement.setString(1, userName);
					preStatement.setString(2, id);
					preStatement.executeUpdate();

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					//该学生已经选了该课
				}
			}
			else
			{
				failCoursesList.add(getCourseNameByCourseId(id));
			}
		}
		return failCoursesList;
	}

	/*
	 * 学生查询选课情况
	 */
	public List<Course> findAllSelectCourse(String userName)
	{
		List<Course> courses = new ArrayList<Course>();
		try {
			preStatement=connection.prepareStatement("select * from course join elective on course.course_id=elective.course_id where elective.username=?");
			preStatement.setString(1,userName);
			resultSet=preStatement.executeQuery();
			while(resultSet.next())
			{
				Course course=new Course(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getInt(4),resultSet.getString(5),resultSet.getString(6),resultSet.getString(7),resultSet.getInt(8));
				courses.add(course);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return courses;
	}

	public List<Course> findAllCourses() //获取课程表信息的List
	{
		List<Course> courseList = new ArrayList<Course>();
		try {
			preStatement=connection.prepareStatement("select * from course");
			resultSet=preStatement.executeQuery();
			while(resultSet.next())
			{
				Course course=new Course(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getInt(4),resultSet.getString(5),resultSet.getString(6),resultSet.getString(7),resultSet.getInt(8));
				courseList.add(course);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return courseList;
	}

	/*
	通过课程名获取课程id
	 */
	public String getCourseIdByCourseName(String courseName)
	{
		String courseId="";
		try {
			preStatement=connection.prepareStatement("select course_id from course where course_name=?");
			preStatement.setString(1, courseName);
			resultSet=preStatement.executeQuery();
			if(resultSet.next())
			{
				courseId=resultSet.getString("course_id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return courseId;

	}

	/*
	通过课程名获取课程id
	 */
	public String getCourseNameByCourseId(String courseId)
	{
		String courseName="";
		try {
			preStatement=connection.prepareStatement("select course_name from course where course_id=?");
			preStatement.setString(1, courseId);
			resultSet=preStatement.executeQuery();
			if(resultSet.next())
			{
				courseName=resultSet.getString("course_name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return  courseName;

	}
	/*
	通过课程id获取该课程剩余可选数剩余
	 */
	public int getRemainCourseByCourseId(String courseId) {
		int maxNumber = 0;
		int availableNumber = 0;
		try {
			preStatement = connection.prepareStatement("select limited from course where course_id=?");
			preStatement.setString(1, courseId);
			resultSet = preStatement.executeQuery();
			if (resultSet.next()) {
				maxNumber = resultSet.getInt("limited");
			}
			preStatement = connection.prepareStatement("select Count(*) from elective where course_id=?");
			preStatement.setString(1, courseId);
			resultSet = preStatement.executeQuery();
			if (resultSet.next()) {
				availableNumber = maxNumber - resultSet.getInt(1);
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return availableNumber;

	}

	/*
	获取已经选课
	 */
	public List<String> getIsSelectedCourse(String userName,String[] courseId)
	{
		List<String> IsSelectedCoursesList=new ArrayList<String>();//重复选择的课程名List
		List<String> IsSelectedCourses=new ArrayList<String>();//已经选课的id
		try {
			preStatement = connection.prepareStatement("select * from elective where username=?");
			preStatement.setString(1, userName);
			resultSet = preStatement.executeQuery();
			while (resultSet.next())
			{
				String id = resultSet.getString("course_id");
				IsSelectedCourses.add(id);
			}
			for(String selectedCourse:IsSelectedCourses)
			{
				for(int i=0;i<courseId.length;i++)
				{
					if(selectedCourse.equals(courseId[i]))
					{
						IsSelectedCoursesList.add(getCourseNameByCourseId(selectedCourse));
					}
				}
			}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			if (IsSelectedCoursesList == null)
			{
				return null;
			}
		return IsSelectedCoursesList;
	}

}


